import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import org.apache.log4j.Logger;

public class QueryOracle {

	private final static String COLUMN_DELIMITER = "|*";	// 列分隔符

	private final static String ROW_DELIMITER = "|$";		// 行分隔符

	public String GetXSwebService(String date, String xh) {
		System.out.println("output request parameters: data = " + date + ",xh =" + xh);
		StringBuffer buff = new StringBuffer();
		try {
			Connection conn = JdbcUtil.getDBConn();
			conn.setAutoCommit(false);  
			
			// First: execute procedure.
			CallableStatement proc = null;
			proc = conn.prepareCall("{ call P_BBTJ_XSTJ_New(?) }");
			proc.setInt(1, new Integer(date).intValue());
			proc.executeUpdate();
			conn.commit();

			// Second: execute query sql.
			String sql = "select JLDH,JLDMC,DL ,BZSM from bb_gg_xstj where tjrq=" + date + " and xh=" + xh + " order by jgbh, bjdid";
			Statement ps = conn.createStatement();
			ResultSet rs = ps.executeQuery(sql);
			System.out.println(sql);
			while (rs.next()) {
				String jldh = rs.getString(1);
				String jldmc = rs.getString(2);
				String dl = rs.getString(3);
				String bzsm = rs.getString(4);

				buff.append(jldh);
				buff.append(COLUMN_DELIMITER);

				buff.append(jldmc);
				buff.append(COLUMN_DELIMITER);

				buff.append(dl);
				buff.append(COLUMN_DELIMITER);

				buff.append(bzsm);
				buff.append(ROW_DELIMITER);

			}
			JdbcUtil.free(rs, ps, conn);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		
		if (buff.length() != 0) {
			return buff.substring(0, buff.length() - 2);	// 去除最后的行分隔符 |$
		} else {
			return "";	// 当结果集为空时，返回空字符串
		}
	}
	
	public String TestService() {
		return "hello";
	}
}
